using System;
using System.Data;
using System.Collections.Generic;
using System.Text;
using System.Collections;
using System.Data.SqlClient;

using DataAccess = PHSRAG.Utility.DataAccess;
using SearchCriteria = PHSRAG.Insight21.SearchContext.SearchCriteria;
using ICachePolicy = PHSRAG.Insight21.Policy.ICachePolicy;
using Utils = PHSRAG.Insight21.InsightUtilities;
using UserContext = PHSRAG.Insight21.Context.UserContext;


namespace PHSRAG.Insight21.Humans
{
    public class ProtocolManager
    {
        #region Constants
        private const string ProtocolManagerSummaryKey = "ProtocolManager_041AB510-B03D-475d-88E1-7F38A9EA65B2";
        private const bool AscendingSortSequence = false;
        private const string FeedDataTableName = "feed";
        /// <summary>The following constant(IdDataTableName)represent the datatable consisting the list of Ids(obtained passing the search criteria).</summary>
        #endregion

        #region Instance Variables
        private bool sortAscending = AscendingSortSequence;
        private string sortColumn = "SubmissionDate";	// Keep this in sync with what the stored proc orders by.
        private string pageURL = string.Empty;
        private DataTable idTable;
        private ICachePolicy cachePolicy;
        #endregion

        #region Constructor
        /// <summary>
        /// Constructor
        /// </summary>
        /// <param name="cachePolicy">cachePolicy to object object loadinga and storing the object in results cache</param>
        public ProtocolManager(ICachePolicy cachePolicy)
        {
            CachePolicy = cachePolicy;
        }
        #endregion

        #region Properties
        /// <summary>Get/Set the cache policy</summary>
        protected ICachePolicy CachePolicy { get { return cachePolicy; } set { cachePolicy = value; } }

        public static string CacheKey
        {
            get { return ProtocolManagerSummaryKey; }
        }

        /// <summary>
        /// Get the total number of protocols.
        /// </summary>
        public int Count
        {
            get
            {
                return (idTable != null) ? idTable.Rows.Count : 0;
            }
        }
        /// <summary>
        /// Get the sort sequence.
        /// </summary>
        public bool SortAscending
        {
            set
            {
                sortAscending = value;
            }
            get { return sortAscending; }
        }

        /// <summary>
        /// Get the name of the sort column.
        /// </summary>
        public string SortColumn
        {
            set
            {
                // sortAscending =(sortColumn == value)? false : true;
                sortColumn = value;
            }
            get { return sortColumn; }
        }
        /// <summary>
        /// get the UserContext
        /// </summary>
        private UserContext CurrentUserContext
        {
            get
            {
                return cachePolicy.GetCachedObject(UserContext.CacheKey) as UserContext;
            }
        }
        /// <summary>
        /// This property will load the dates of the Latest feeds that were run for various modules in to application cache.
        /// </summary>
        public static DataTable LastFeedRunDate
        {
            get
            {
                return CDS.RefCode.GetLastFeedDates();
            }
        }

        /// <summary>
        /// This property will be used by the SQL reports
        /// </summary>
        public string PageURL
        {
            get
            {
                return pageURL;
            }
            set
            {
                pageURL = value;
            }
        }
        #endregion

        #region Public methods

        /* at098 - 1/18/2008
         * used by sqlReportHost - protoType*/
        //public string GetDelimitedIds(int columnIndex)
        //{
        //    StringBuilder sb = new StringBuilder();
        //    foreach (DataRow row in idTable.Rows)
        //    {
        //        if (sb.ToString().Equals(string.Empty))
        //            sb.Append(row.ItemArray[columnIndex]);
        //        else
        //            sb.Append("," + row.ItemArray[columnIndex]);
        //    }
        //    return sb.ToString();
        //}

        #region Tracking page Ids retrieval
        /// <summary>
        /// This method will get the list of Protocol Ids matching the search criteria passed in.
        /// The strored procedure call from this method also sorts the result on the required column,
        /// the default being the Submission date.
        /// </summary>
        /// <param name="connectionString"> Connection string for the database</param>
        /// <param name="searchCriteria">object containing the search values(eneterd in the UI)</param>
        /// <param name="args">Arguments parameters for search and sort</param>
        public void GetActiveInactiveProtocolIds(string connectionString, SearchCriteria searchCriteria, params object[] args)
        {
            try
            {
                if (args.Length < 3)
                    throw new Exception("GetActiveProtocolIds method requires a domain user ID ,originating context and module flag ");

                DataTable DataContainer = DataAccess.GetDataTable(connectionString, "dbo.[GetActiveInactiveProtocolIds]",
                     DataAccess.BuildSqlParameterArray
                     ("@domainUserID", SqlDbType.Int, (int)args[0],
                     "@originatingpage", SqlDbType.Int, args[1],
                     "@module", SqlDbType.Char, args[2],
                     "@protocolNo", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolNumberKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolNumberKey].TrimEnd('*'),
                     "@protocolTitle", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolTitleKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolTitleKey].TrimEnd('*'),
                     "@sponsorName", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolSponsorKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolSponsorKey].TrimEnd('*'),
                     "@versionNo", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.VersionKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.VersionKey].TrimEnd('*'),
                     "@expDateFrom", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpStartDateKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpStartDateKey].TrimEnd('*'),
                     "@expDateTo", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpEndDateKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpEndDateKey].TrimEnd('*'),
                     "@piId", SqlDbType.Int, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolPIKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolPIKey].TrimEnd('*'),
                     "@cosIds", SqlDbType.NText, (null == searchCriteria.COSIds || string.Empty == searchCriteria.COSIds) ? (object)DBNull.Value : searchCriteria.COSIds,
                     "@species", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.SpeciesKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.SpeciesKey].TrimEnd('*'),
                     "@isRadioIsotope", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.RadioIsotopeKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.RadioIsotopeKey] == "True",
                     "@isBiologicalAgent", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.BiologicalAgentKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.BiologicalAgentKey] == "True",
                     "@isToxicChemicals", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ToxicChemicalsKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ToxicChemicalsKey] == "True",
                     "@isTissueTest", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.TissueTestKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.TissueTestKey] == "True",
                     "@sortColumn", SqlDbType.VarChar, sortColumn,
                     "@sortOrder", SqlDbType.Bit, sortAscending
                     ));

                if ((DataContainer != null) && (DataContainer.Rows.Count != 0))
                {
                    //idList = new ArrayList(DataContainer.Rows.Count);
                    //foreach (DataRow dr in DataContainer.Rows)
                    //{
                    //    idList.Add(dr[0]);
                    //}
                    idTable = DataContainer;
                    Policy.ResultsCache.SetObject(cachePolicy, ProtocolManagerSummaryKey, this);
                }
            }
            catch (Exception ex)
            {
                UserContext usercontext = CurrentUserContext;
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.GetActiveInactiveProtocolIds: Failed to get the Active Or Inactive Protocol Ids ", ex, true, Utility.ExceptionReport.ReportType.Error);
                if (usercontext != null)
                    Utility.ExceptionReport.WriteToEventLog(string.Format(" for the logged in User {0}, {1} with username ({2})",
                    usercontext.LastName, usercontext.FirstName, usercontext.Username), ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
        }


        /// <summary>
        /// This method will get the list of Protocol Ids matching the search criteria passed in.
        /// The strored procedure call from this method also sorts the result on the required column,
        /// the default being the Submission date.
        /// </summary>
        /// <param name="connectionString"> Connection string for the database</param>
        /// <param name="searchCriteria">object containing the search values(eneterd in the UI)</param>
        /// <param name="args">Arguments parameters for search and sort</param>
        public void GetPendingApplicationIds(string connectionString, SearchCriteria searchCriteria, params object[] args)
        {
            try
            {
                if (args.Length < 2)
                    throw new Exception("GetPendingItemIds method requires a domain user ID");

                DataTable DataContainer = DataAccess.GetDataTable(connectionString, "dbo.[GetPendingItemIds]",
                     DataAccess.BuildSqlParameterArray
                     ("@domainUserID", SqlDbType.Int, (int)args[0],
                     "@module", SqlDbType.Char, args[1],
                     "@protocolNo", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolNumberKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolNumberKey].TrimEnd('*'),
                     "@protocolTitle", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolTitleKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolTitleKey].TrimEnd('*'),
                     "@sponsorName", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolSponsorKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolSponsorKey].TrimEnd('*'),
                     "@versionNo", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.VersionKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.VersionKey].TrimEnd('*'),
                     "@expDateFrom", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpStartDateKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpStartDateKey].TrimEnd('*'),
                     "@expDateTo", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpEndDateKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpEndDateKey].TrimEnd('*'),
                     "@piId", SqlDbType.Int, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolPIKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolPIKey].TrimEnd('*'),
                     "@cosIds", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolCOSKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolCOSKey].TrimEnd('*'),
                     "@species", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.SpeciesKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.SpeciesKey].TrimEnd('*'),
                     "@isRadioIsotope", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.RadioIsotopeKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.RadioIsotopeKey] == "True",
                     "@isBiologicalAgent", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.BiologicalAgentKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.BiologicalAgentKey] == "True",
                     "@isToxicChemicals", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ToxicChemicalsKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ToxicChemicalsKey] == "True",
                     "@isTissueTest", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.TissueTestKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.TissueTestKey] == "True",
                     "@sortColumn", SqlDbType.VarChar, sortColumn,
                     "@sortOrder", SqlDbType.Bit, sortAscending
                     ));

                if ((DataContainer != null) && (DataContainer.Rows.Count != 0))
                {
                    //idList = new ArrayList(DataContainer.Rows.Count);
                    //foreach (DataRow dr in DataContainer.Rows)
                    //{
                    //    idList.Add(dr[0]);
                    //}
                    idTable = DataContainer;
                    Policy.ResultsCache.SetObject(cachePolicy, ProtocolManagerSummaryKey, this);
                }
            }
            catch (Exception ex)
            {
                UserContext usercontext = CurrentUserContext;
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.GetPendingApplicationIds: Failed to get the Pending item Ids", ex, true, Utility.ExceptionReport.ReportType.Error);
                if (usercontext != null)
                    Utility.ExceptionReport.WriteToEventLog(string.Format(" for the logged in user {0}, {1} with username ({2})",
                        usercontext.LastName, usercontext.FirstName, usercontext.Username), ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
        }

        /// <summary>
        /// This method will get the list of Protocol Ids matching the search criteria passed in.
        /// The strored procedure call from this method also sorts the result on the required column,
        /// the default being the Submission date.
        /// </summary>
        /// <param name="connectionString"> Connection string for the database</param>
        /// <param name="searchCriteria">object containing the search values(eneterd in the UI)</param>
        /// <param name="args">Arguments parameters for search and sort</param>
        public void GetWIPProtocolProcessIds(string connectionString, SearchCriteria searchCriteria, params object[] args)
        {
            try
            {
                if (args.Length < 2)
                    throw new Exception("GetWIPProtocolProcessIds method requires a domain user ID");

                DataTable DataContainer = DataAccess.GetDataTable(connectionString, "dbo.[GetWIPProtocolProcessIds]",
                     DataAccess.BuildSqlParameterArray
                     ("@domainUserID", SqlDbType.Int, (int)args[0],
                     "@moduleType", SqlDbType.Char, args[1],
                     "@protocolNumber", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolNumberKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolNumberKey].TrimEnd('*'),
                     "@protocolTitle", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolTitleKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolTitleKey].TrimEnd('*'),
                     "@piId", SqlDbType.Int, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolPIKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolPIKey].TrimEnd('*'),
                     "@organizationIds", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolCOSKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolCOSKey].TrimEnd('*'),
                     "@sortColumn", SqlDbType.VarChar, sortColumn,
                     "@sortOrder", SqlDbType.Bit, sortAscending
                     ));

                if ((DataContainer != null) && (DataContainer.Rows.Count != 0))
                {
                    //idList = new ArrayList(DataContainer.Rows.Count);
                    //foreach (DataRow dr in DataContainer.Rows)
                    //{
                    //    idList.Add(dr[0]);
                    //}
                    idTable = DataContainer;
                    Policy.ResultsCache.SetObject(cachePolicy, ProtocolManagerSummaryKey, this);
                }
            }
            catch (Exception ex)
            {
                UserContext usercontext = CurrentUserContext;
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.GetWIPProtocolProcessIds: Failed to get the Work In Progress protocol process Ids", ex, true, Utility.ExceptionReport.ReportType.Error);
                if (usercontext != null)
                    Utility.ExceptionReport.WriteToEventLog(string.Format(" for the logged in user {0}, {1} with username ({2})",
                        usercontext.LastName, usercontext.FirstName, usercontext.Username), ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
        }

        /// <summary>
        /// This method will get the list of Exempt Protocol Ids.
        /// The strored procedure call from this method also sorts the result on the required column,
        /// the default being the Submission date.
        /// </summary>
        /// <param name="connectionString"> Connection string for the database</param>
        /// <param name="searchCriteria">object containing the search values(eneterd in the UI)</param>
        /// <param name="args">Arguments parameters for search and sort</param>
        public void GetExemptProtocolIds(string connectionString, SearchCriteria searchCriteria, params object[] args)
        {
            try
            {
                if (args.Length < 2)
                    throw new Exception("GetExemptProtocolIds method requires a domain user ID");

                DataTable DataContainer = DataAccess.GetDataTable(connectionString, "dbo.[GetExemptProtocolIds]",
                     DataAccess.BuildSqlParameterArray
                     ("@domainUserID", SqlDbType.Int, (int)args[0],
                     "@module", SqlDbType.Char, args[1],
                     "@protocolNo", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolNumberKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolNumberKey].TrimEnd('*'),
                     "@protocolTitle", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolTitleKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.PrimaryProtocolSearch.ProtocolTitleKey].TrimEnd('*'),
                     "@sponsorName", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolSponsorKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolSponsorKey].TrimEnd('*'),
                     "@versionNo", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.VersionKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.VersionKey].TrimEnd('*'),
                     "@expDateFrom", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpStartDateKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpStartDateKey].TrimEnd('*'),
                     "@expDateTo", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpEndDateKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolExpEndDateKey].TrimEnd('*'),
                     "@piId", SqlDbType.Int, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolPIKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolPIKey].TrimEnd('*'),
                     "@cosIds", SqlDbType.NText, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolCOSKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ProtocolCOSKey].TrimEnd('*'),
                     "@species", SqlDbType.VarChar, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.SpeciesKey]) ? (object)DBNull.Value : searchCriteria[Utils.Constants.AdvancedProtocolSearch.SpeciesKey].TrimEnd('*'),
                     "@isRadioIsotope", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.RadioIsotopeKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.RadioIsotopeKey] == "True",
                     "@isBiologicalAgent", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.BiologicalAgentKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.BiologicalAgentKey] == "True",
                     "@isToxicChemicals", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.ToxicChemicalsKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.ToxicChemicalsKey] == "True",
                     "@isTissueTest", SqlDbType.Bit, (null == searchCriteria[Utils.Constants.AdvancedProtocolSearch.TissueTestKey]) ? false : searchCriteria[Utils.Constants.AdvancedProtocolSearch.TissueTestKey] == "True",
                     "@sortColumn", SqlDbType.VarChar, sortColumn,
                     "@sortOrder", SqlDbType.Bit, sortAscending
                     ));

                if ((DataContainer != null) && (DataContainer.Rows.Count != 0))
                {
                    //idList = new ArrayList(DataContainer.Rows.Count);
                    //foreach (DataRow dr in DataContainer.Rows)
                    //{
                    //    idList.Add(dr[0]);
                    //}
                    idTable = DataContainer;
                    Policy.ResultsCache.SetObject(cachePolicy, ProtocolManagerSummaryKey, this);
                }
            }
            catch (Exception ex)
            {
                UserContext usercontext = CurrentUserContext;
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.GetWIPProtocolProcessIds: Failed to get the Work In Progress protocol process Ids", ex, true, Utility.ExceptionReport.ReportType.Error);
                if (usercontext != null)
                    Utility.ExceptionReport.WriteToEventLog(string.Format(" for the logged in user {0}, {1} with username ({2})",
                        usercontext.LastName, usercontext.FirstName, usercontext.Username), ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
        }
        #endregion

        #region Tracking page data retrieval
        /// <summary>
        /// This method will get the list of protocols by consuming the Ids Passed to Datacontainer
        /// </summary>
        /// <param name="startIndex"> Starting index for the list of Ids to be passed</param>
        /// <param name="endIndex">Ending index for the list of Ids to be passed</param>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="domainUserId">Logged in domain User Id</param>
        /// <returns>Array list of Protocol objects</returns>
        //public ArrayList GetProtocolDetails(int startIndex, int endIndex, string connectionString, int domainUserId)
        //{
        //    ArrayList protocolList = null;
        //    try
        //    {

        //        string delimittedString = GetDelimitedIds(startIndex, endIndex, 0);
        //        if (delimittedString != string.Empty)
        //        {
        //            // protocolList = new ArrayList(endIndex);
        //            DataTable dataContainer = DataAccess.GetDataTable(
        //                connectionString, "dbo.[GetProtocols]", DataAccess.BuildSqlParameterArray(
        //                "@delimittedString", SqlDbType.VarChar, delimittedString,
        //                "@domainUserId", SqlDbType.Int, domainUserId));

        //            if ((dataContainer != null) && (dataContainer.Rows.Count > 0))
        //            {
        //                protocolList = new ArrayList(dataContainer.Rows.Count);

        //                foreach (DataRow dr in dataContainer.Rows)
        //                {
        //                    protocolList.Add(new Protocol(cachePolicy, dr));
        //                }
        //            }
        //        }

        //    }
        //    catch (Exception ex)
        //    {
        //        UserContext usercontext = CurrentUserContext;
        //        Utility.ExceptionReport.WriteToEventLog("Protocolmanager.GetProtocolDetails: Failed to get the active or inactive protocoldetails", ex, true, Utility.ExceptionReport.ReportType.Error);
        //        if (usercontext != null)
        //            Utility.ExceptionReport.WriteToEventLog(string.Format(" for the logged in User {0}, {1} with username ({2})",
        //                usercontext.LastName, usercontext.FirstName, usercontext.Username), ex, true, Utility.ExceptionReport.ReportType.Error);
        //        throw new Exception("Error occured while trying to retrieve data");
        //    }
        //    return protocolList;

        //}

        /// <summary>
        /// This method will get the list of protocols by consuming the Ids Passed to Datacontainer
        /// </summary>
        /// <param name="startIndex"> Starting index for the list of Ids to be passed</param>
        /// <param name="endIndex">Ending index for the list of Ids to be passed</param>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="domainUserId">Logged in domain User Id</param>
        /// <returns>datareader</returns>
        public SqlDataReader GetProtocolDetails(int startIndex, int endIndex, string connectionString, int domainUserId)
        {
            SqlDataReader protocolDetailsReader = null;
            try
            {

                string delimittedString = GetDelimitedIds(startIndex, endIndex, 0);
                if (delimittedString != string.Empty)
                {
                    // protocolList = new ArrayList(endIndex);
                    protocolDetailsReader = DataAccess.ExecuteReader(
                        connectionString, "dbo.[GetProtocols]", DataAccess.BuildSqlParameterArray(
                        "@delimittedString", SqlDbType.VarChar, delimittedString,
                        "@domainUserId", SqlDbType.Int, domainUserId));
                }

            }
            catch (Exception ex)
            {
                UserContext usercontext = CurrentUserContext;
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.GetProtocolDetails: Failed to get the active or inactive protocoldetails", ex, true, Utility.ExceptionReport.ReportType.Error);
                if (usercontext != null)
                    Utility.ExceptionReport.WriteToEventLog(string.Format(" for the logged in User {0}, {1} with username ({2})",
                        usercontext.LastName, usercontext.FirstName, usercontext.Username), ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
            return protocolDetailsReader;
        }

        /// <summary>
        /// This method will get the list of pending applications by consuming the Ids in the Datacontainer
        /// </summary>
        /// <param name="startIndex"> Starting index for the list of Ids to be passed</param>
        /// <param name="endIndex">Ending index for the list of Ids to be passed</param>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="domainUserId">Logged in domain User Id</param>
        /// <returns>Array list of PendingApplicationSummary objects</returns>
        public ArrayList GetPendingItems(int startIndex, int endIndex, string connectionString, int domainUserId)
        {
            ArrayList protocolList = null;
            try
            {

                string protocolDelimittedString = GetDelimitedIds(startIndex, endIndex, 0);
                string protocolProcessDelimittedString = GetDelimitedIds(startIndex, endIndex, 1);
                string boardReviewIdDelimittedString = GetDelimitedIds(startIndex, endIndex, 2);
                if (protocolDelimittedString == string.Empty && protocolProcessDelimittedString == string.Empty)
                {
                    return protocolList;
                }
                // protocolList = new ArrayList(endIndex);
                DataTable dataContainer = DataAccess.GetDataTable(
                    connectionString, "dbo.[GetPendingItemsSummary]", DataAccess.BuildSqlParameterArray(
                    "@protocolIdDelimittedString", SqlDbType.VarChar, protocolDelimittedString,
                    "@protocolProcessIdDelimittedString", SqlDbType.VarChar, protocolProcessDelimittedString,
                    "@boardReviewIdDelimittedString", SqlDbType.VarChar, boardReviewIdDelimittedString,
                    "@domainUserId", SqlDbType.Int, domainUserId));

                if ((dataContainer != null) && (dataContainer.Rows.Count > 0))
                {
                    protocolList = new ArrayList(dataContainer.Rows.Count);

                    foreach (DataRow dr in dataContainer.Rows)
                    {
                        protocolList.Add(new PendingApplicationSummary(cachePolicy, dr));
                    }
                }
            }
            catch (Exception ex)
            {
                UserContext usercontext = CurrentUserContext;
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.GetPendingItems: Failed to get the pending application details", ex, true, Utility.ExceptionReport.ReportType.Error);
                if (usercontext != null)
                    Utility.ExceptionReport.WriteToEventLog(string.Format("for the logged in User {0}, {1} with username ({2})",
                        usercontext.LastName, usercontext.FirstName, usercontext.Username), ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
            return protocolList;
        }
        /// <summary>
        /// This method will get the list of Work in progress processes consuming the Ids in the IdTable
        /// </summary>
        /// <param name="startIndex"> Starting index for the list of Ids to be passed</param>
        /// <param name="endIndex">Ending index for the list of Ids to be passed</param>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="domainUserId">Logged in domain User Id</param>
        /// <returns>DataTable containing the list work in progress</returns>
        public SqlDataReader GetWIPProtocolProcesses(int startIndex, int endIndex, string connectionString, int domainUserId)
        {
            SqlDataReader workInProgressReader = null;
            try
            {

                string delimittedString = GetDelimitedIds(startIndex, endIndex, 0);
                if (delimittedString != string.Empty)
                {
                    workInProgressReader = DataAccess.ExecuteReader(
                        connectionString, "dbo.[GetWIPProtocolProcesses]", DataAccess.BuildSqlParameterArray(
                        "@delimittedString", SqlDbType.VarChar, delimittedString,
                        "@domainUserId", SqlDbType.Int, domainUserId));
                }
            }
            catch (Exception ex)
            {
                UserContext usercontext = CurrentUserContext;
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.GetWIPProtocolProcesses: Failed to get the Work In Progress", ex, true, Utility.ExceptionReport.ReportType.Error);
                if (usercontext != null)
                    Utility.ExceptionReport.WriteToEventLog(string.Format(" for the logged in User {0}, {1} with username ({2})",
                        usercontext.LastName, usercontext.FirstName, usercontext.Username), ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
            return workInProgressReader;
        }

        /// <summary>
        /// This method will be used by the Download to Excel and Pdf.
        /// The list of Ids available in the results cache will be concatenated to form a delimitted string that will be 
        /// passed in as input to respective stored proc to get the output
        /// Note: Not used as the reports were converted to SQl Server Reporting Services.
        /// </summary>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="domainUserId">Logged in domain User Id</param>
        /// <returns></returns>
        public DataSet GetDetailsForDownload(string connectionString, int domainUserId, string page)
        {
            DataSet dataContainer = null;
            try
            {
                string storedProc = string.Empty;
                switch (page.ToLower())
                {
                    case "activeprotocols.aspx":
                    case "inactiveprotocols.aspx":
                    case "exemptprotocols.aspx":
                        storedProc = "dbo.GetProtocols";
                        break;
                    case "pendingapplications.aspx":
                        storedProc = "dbo.GetPendingItemsSummary";
                        break;
                    case "workinprogresspage.aspx":
                        storedProc = "dbo.GetWIPProtocolProcesses";
                        break;
                    default:
                        break;
                }
                if (storedProc != string.Empty)
                    dataContainer = DataAccess.GetDataSet(
                               connectionString, storedProc, DataAccess.BuildSqlParameterArray(
                               "@delimittedString", SqlDbType.VarChar, GetDelimitedIds(0, idTable.Rows.Count, 0),
                               "@domainUserId", SqlDbType.Int, domainUserId));
                else
                {
                    throw new Exception("Stored procedure was not set");
                }
            }
            catch (Exception ex)
            {
                UserContext usercontext = CurrentUserContext;
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.GetDetailsForDownload: Failed to get the details for download", ex, true, Utility.ExceptionReport.ReportType.Error);
                if (usercontext != null)
                    Utility.ExceptionReport.WriteToEventLog(string.Format("for the logged in User {0}, {1} with username ({2})",
                        usercontext.LastName, usercontext.FirstName, usercontext.Username), ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
            return dataContainer;
        }
           

        /// <summary>
        /// This method will construct a comma delimited string for the Ids in the IdTable depending on the start & end index.
        /// </summary>
        /// <param name="startIndex"> Starting index for the list of Ids to be passed</param>
        /// <param name="endIndex">Ending index for the list of Ids to be passed</param>
        /// <param name="columnIndex">Column index from which the delimitted string has to be constructed.</param>
        /// <returns>comma delimitted string</returns>
        public string GetDelimitedIds(int startIndex, int endIndex, int columnIndex)
        {
            StringBuilder delimitedString = null;
            try
            {
                if (idTable != null && idTable.Rows.Count > 0)
                {
                    delimitedString = new StringBuilder();
                    for (int index = startIndex; index < endIndex; ++index)
                    {
                        if (index == startIndex)//  delimittedString.ToString().Equals(string.Empty))
                        {
                            delimitedString.Append(idTable.Rows[index].ItemArray[columnIndex].ToString());
                        }
                        else
                        {
                            delimitedString.Append("," + idTable.Rows[index].ItemArray[columnIndex]);
                        }
                    }
                }

            }
            catch (Exception ex)
            {
                UserContext usercontext = CurrentUserContext;
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.GetDelimitedIds: Failed to construct the delimitted string of Ids", ex, true, Utility.ExceptionReport.ReportType.Error);
                if (usercontext != null)
                    Utility.ExceptionReport.WriteToEventLog(string.Format("for the logged in User {0}, {1} with username ({2})",
                        usercontext.LastName, usercontext.FirstName, usercontext.Username), ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
            return (delimitedString == null) ? string.Empty : delimitedString.ToString();
        }

        #endregion
        #endregion

        #region Public static methods
        /// <summary>
        /// Saves the Notification letters in to the document table and adds the ampping for the same in the Protocoldetails_document_Map
        /// </summary>
        /// <param name="protocolProcessId">processId</param>
        /// <param name="boardId">boardId</param>
        /// <param name="boardStatus">boardStatus</param>
        /// <param name="notificationDocumentId">notificationDocumentId</param>
        /// <param name="loggedInAdmPersonId">AdmpersonId that is saving/modifying the document</param>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="protocolNumber">Protocol number associated with</param>
        public static void SaveNotificationLetter(int protocolProcessId, int boardId, string boardStatus, int notificationDocumentId,int loggedInAdmPersonId,string connectionString, string protocolNumber)
        {
            try
            {
                Utility.DataAccess.ExecuteNonQuery(connectionString
                    , "SaveNotificationDocumentMap"
                    , Utility.DataAccess.BuildSqlParameterArray
                    ( "@protocolProcessId", SqlDbType.Int, protocolProcessId
                    , "@boardId", SqlDbType.Int, boardId
                    , "@boardStatus", SqlDbType.VarChar, boardStatus
                    , "@documentId", SqlDbType.Int, notificationDocumentId
                    , "@createdByAdmPersonId", SqlDbType.VarChar, loggedInAdmPersonId
                    , "@protocolNumber", SqlDbType.VarChar, protocolNumber
                    ));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.SaveNotificationLetter: Failed to save the notification letter", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
        }

        /// <summary>
        /// Deletes the document & removes the mapping from the protocoldetails_document_Map table
        /// </summary>
        /// <param name="protocolProcessId">processId</param>
        /// <param name="boardId">boardId</param>
        /// <param name="boardStatus">boardStatus</param>
        /// <param name="notificationDocumentId">notificationDocumentId</param>
        /// <param name="connectionString">Database connection string</param>
        /// <param name="protocolNumber">Protocol number associated with</param>
        public static void DeleteNotificationLetter(int protocolProcessId, int boardId, string boardStatus, int notificationDocumentId, string connectionString, string protocolNumber)
        {
            try
            {
                Utility.DataAccess.ExecuteNonQuery(connectionString
                    , "DeleteNotificationDocument"
                    , Utility.DataAccess.BuildSqlParameterArray
                    ( "@protocolNumber", SqlDbType.VarChar,protocolNumber
                    , "@protocolProcessId", SqlDbType.Int, protocolProcessId
                    , "@boardId", SqlDbType.Int, boardId
                    , "@boardStatus", SqlDbType.VarChar, boardStatus
                    , "@documentId", SqlDbType.Int, notificationDocumentId                    
                    ));
            }
            catch (Exception ex)
            {
                Utility.ExceptionReport.WriteToEventLog("Protocolmanager.DeleteNotificationLetter: Failed to Delete the notification letter", ex, true, Utility.ExceptionReport.ReportType.Error);
                throw new Exception("Error occured while trying to retrieve data");
            }
        }
        #endregion


    }
}

